#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : target_data.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-11-18 09:39 
@Desc    : 处理wh_house_cal中的目标数据
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util

all_rooms_sql = """select house_id,city_name,work_name from youjia_report.wh_house_static"""
all_rooms_df = pd.read_sql_query(all_rooms_sql, db_util.con_youjia)


def target_12(file_name="2019-12目标.xlsx"):
    """
    2019年12月按门店更新目标
    :param file_name:
    :return:
    """
    update_sql_tmp = """
                update youjia_report.wh_house_calendar set target_check_rate = {target_check_rate} ,target_adr={target_adr} ,
                target_gmv = {target_gmv}
                where house_id in {house_ids}
                and dt between '{st}' and '{et}';
                """
    target_excel_df = pd.read_excel(file_name)
    for idx, row in target_excel_df.iterrows():
        """{'区域': nan, '城市': '绍兴', '门店': '绍兴分店', 
        '运营经理': '刘炳佑', '房量': 10, '目标可售入住率': 0.65, 
        '目标ADR': 230, '目标套均GMV': 4402.775}"""
        work_name = row['门店']
        work_rooms_df = all_rooms_df[all_rooms_df.work_name == work_name].copy()
        target_adr = round(row['目标ADR']*100)
        target_gmv = round(row['目标套均GMV']*100)
        target_check_rate = round(row['目标可售入住率']*10000)
        work_house_ids = tuple(work_rooms_df.house_id.unique())
        # print(work_house_ids)
        update_sql = update_sql_tmp.format(target_check_rate=target_check_rate, target_adr=target_adr,
                                           target_gmv=target_gmv, house_ids=work_house_ids, st='2019-12-01',
                                           et='2019-12-31')
        print(update_sql)
        print()
        # db_util.con_mysql_report.insert(update_sql)
        # print(work_name, '\t', len(work_rooms_df), '\t', target_adr, '\t', target_gmv, '\t', target_check_rate)


if __name__ == '__main__':
    target_12()
